﻿using iTool.Common;
using iTool.Common.Options;
using Orleans.Configuration;
using System;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;

namespace iTool.ClusterComponent
{
    public class AdoNetCheckDataStructures
    {
        readonly AdoNetOptions options;
		string masterConnectionString; 
        public AdoNetCheckDataStructures(AdoNetOptions options)
        {
            this.options = options;
			this.masterConnectionString = this.options.GetConnection("master");
		}

        public async Task<bool> CheckDatabaseExistsAsync(string databaseName)
        {
			iPrint.Working($"iTool> Check {databaseName} database exists...");
            await using (SqlConnection conn = new SqlConnection(this.masterConnectionString))
            {
                string script = $"SELECT database_id from sys.databases WHERE Name  = '{databaseName}'";
                await using (SqlCommand sqlCmd = new SqlCommand(script, conn))
                {
                    await conn.OpenAsync();
                    object resultObj = await sqlCmd.ExecuteScalarAsync();
                    int databaseID = 0;
                    if (resultObj != null)
                    {
                        int.TryParse(resultObj.ToString(), out databaseID);
                    }

                    if (databaseID > 0)
                    {
						iPrint.Success($"iTool> Check {databaseName} database exists true.");
                    }
                    else
                    {
						iPrint.Error($"iTool> Check {databaseName} database exists false.");
					}

					
                    return databaseID > 0;
                }
            }
        }

		public async Task<bool> CheckTableExistsAsync(string tableName)
		{
			iPrint.Working($"iTool> Check {tableName} Table exists...");
			await using (SqlConnection conn = new SqlConnection(this.options.GetConnection()))
			{
				string script = $"SELECT 1 FROM information_schema.TABLES WHERE table_name  = '{tableName}'";
				await using (SqlCommand sqlCmd = new SqlCommand(script, conn))
				{
					await conn.OpenAsync();
					object resultObj = await sqlCmd.ExecuteScalarAsync();
					int databaseID = 0;
					if (resultObj != null)
					{
						int.TryParse(resultObj.ToString(), out databaseID);
					}
					if (databaseID > 0)
					{
						iPrint.Success($"iTool> Check {tableName} Table exists true.");
					}
					else
					{
						iPrint.Error($"iTool> Check {tableName} Table exists false.");
					}
					return databaseID > 0;
				}
			}
		}

		public async Task CreateDatabaseAsync(string databaseName)
		{
			iPrint.Working($"iTool> Create {databaseName} database...");
			await using (SqlConnection conn = new SqlConnection(this.masterConnectionString))
			{
				await using (SqlCommand command = new SqlCommand($@"CREATE DATABASE [{databaseName}]", conn))
				{
					await conn.OpenAsync();
					await command.ExecuteNonQueryAsync();
				}
			}
			iPrint.Success($"iTool> Create {databaseName} database structer success.");
		}

		public async Task CreateDatabaseStructerAsync()
		{

			iPrint.Working("iTool> Create database structer...");

            try
            {
                await using (SqlConnection conn = new SqlConnection(this.options.GetConnection(this.options.DatabaseName)))
                {
                    await using (SqlCommand command = new SqlCommand(SQLBuilder().ToString(), conn))
                    {
                        await conn.OpenAsync();
                        await command.ExecuteNonQueryAsync();
                    }
                }
				iPrint.Success("iTool> Create database structer success.");
            }
            catch (Exception ex)
            {
				iPrint.Error($"iTool> Create database structer error {ex.Message}.");
            }
        }

		public async Task ClearNoActiveTimeNoderAsync() 
		{
			iPrint.Working("iTool> Clear no active noder...");

			try
			{
				await using (SqlConnection conn = new SqlConnection(this.options.GetConnection(this.options.DatabaseName)))
				{
					var timeout = DateTime.UtcNow.AddSeconds(-60);
					await using (SqlCommand command = new SqlCommand($"UPDATE [ClusterMembershipTable] SET [Status] = 6 WHERE [IAmAliveTime]  < '{timeout}'", conn))
					{
						await conn.OpenAsync();
						await command.ExecuteNonQueryAsync();
					}
				}
				iPrint.Success("iTool> Clear no active noder success.");
			}
			catch (Exception ex)
			{
				iPrint.Error($"iTool> Clear no active noder error {ex.Message}.");
			}
		}

        private StringBuilder SQLBuilder()
        {
            StringBuilder builder = new StringBuilder();

            // 01
            builder.AppendLine($@"ALTER DATABASE [{this.options.DatabaseName}] SET READ_COMMITTED_SNAPSHOT ON; 
                                ALTER DATABASE [{this.options.DatabaseName}] SET ALLOW_SNAPSHOT_ISOLATION ON;");

            builder.AppendLine(@"
                            CREATE TABLE OrleansQuery
                            (
	                            QueryKey VARCHAR(64) NOT NULL,
	                            QueryText VARCHAR(8000) NOT NULL,
	                            CONSTRAINT OrleansQuery_Key PRIMARY KEY(QueryKey)
                            );");

			builder.AppendLine(@"

							CREATE TABLE ClusterCacheTable
							(
								[key] varchar(150) primary key not null,
								[value] NVARCHAR(max)
							);


							CREATE TABLE ClusterCacheHashTable
							(
								[key] varchar(100) not null,
								[field] varchar(100) not null,
								[value] NVARCHAR(max)
							);
							CREATE NONCLUSTERED INDEX [NonClusteredIndex-20211129-211711] ON [ClusterCacheHashTable]
							(
								[key] ASC,
								[field] ASC
							)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

							CREATE TABLE ClusterCacheSetTable
							(
								[id] int identity(1,1) not null,
								[key] varchar(100) primary key not null,
								[date] DATETIME default getdate()  
							);

							CREATE TABLE ClusterCacheSetValueTable
							(
								[id] int identity(1,1) not null,
								[listID] int,
								[value]  NVARCHAR(max),
								[date] DATETIME default getdate()
							);
							CREATE NONCLUSTERED INDEX [NonClusteredIndex-20211129-214838] ON [ClusterCacheSetValueTable]
							(
								[listID] ASC
							)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

							CREATE TABLE ClusterCacheZSetTable
							(
								[id] int identity(1,1) not null,
								[key] varchar(100) primary key not null,
								[date] DATETIME default getdate()  
							);

							CREATE TABLE ClusterCacheZSetValueTable
							(
								[id] int identity(1,1) not null,
								[listID] int,
								[value]  NVARCHAR(max),
								[sortIndex] int,
								[date] DATETIME default getdate()
							);");

			builder.AppendLine(@"
                CREATE TABLE ClusterMembershipVersionTable
                (
	                DeploymentId NVARCHAR(150) NOT NULL,
	                Timestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(),
	                Version INT NOT NULL DEFAULT 0,
	                CONSTRAINT PK_ClusterMembershipVersionTable_DeploymentId PRIMARY KEY(DeploymentId)
                );

                CREATE TABLE ClusterMembershipTable
                (
	                DeploymentId NVARCHAR(150) NOT NULL,
	                Address VARCHAR(45) NOT NULL,
	                Port INT NOT NULL,
	                Generation INT NOT NULL,
	                SiloName NVARCHAR(150) NOT NULL,
	                HostName NVARCHAR(150) NOT NULL,
	                Status INT NOT NULL,
	                ProxyPort INT NULL,
	                SuspectTimes VARCHAR(8000) NULL,
	                StartTime DATETIME2(3) NOT NULL,
	                IAmAliveTime DATETIME2(3) NOT NULL,
	                CONSTRAINT PK_MembershipTable_DeploymentId PRIMARY KEY(DeploymentId, Address, Port, Generation),
	                CONSTRAINT FK_MembershipTable_MembershipVersionTable_DeploymentId FOREIGN KEY (DeploymentId) REFERENCES ClusterMembershipVersionTable (DeploymentId)
                );
                INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'UpdateIAmAlivetimeKey','
					SET NOCOUNT ON;
					UPDATE ClusterMembershipTable
					SET
						IAmAliveTime = @IAmAliveTime
					WHERE
						DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
						AND Address = @Address AND @Address IS NOT NULL
						AND Port = @Port AND @Port IS NOT NULL
						AND Generation = @Generation AND @Generation IS NOT NULL;
				');

				INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'InsertMembershipVersionKey','
					SET NOCOUNT ON;
					INSERT INTO ClusterMembershipVersionTable
					(
						DeploymentId
					)
					SELECT @DeploymentId
					WHERE NOT EXISTS
					(
						SELECT 1
						FROM
							ClusterMembershipVersionTable WITH(HOLDLOCK, XLOCK, ROWLOCK)
						WHERE
							DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
					);

					SELECT @@ROWCOUNT;
				');

				INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'InsertMembershipKey','
					SET XACT_ABORT, NOCOUNT ON;
					DECLARE @ROWCOUNT AS INT;
					BEGIN TRANSACTION;
					INSERT INTO ClusterMembershipTable
					(
						DeploymentId,
						Address,
						Port,
						Generation,
						SiloName,
						HostName,
						Status,
						ProxyPort,
						StartTime,
						IAmAliveTime
					)
					SELECT
						@DeploymentId,
						@Address,
						@Port,
						@Generation,
						@SiloName,
						@HostName,
						@Status,
						@ProxyPort,
						@StartTime,
						@IAmAliveTime
					WHERE NOT EXISTS
					(
						SELECT 1
						FROM
							ClusterMembershipTable WITH(HOLDLOCK, XLOCK, ROWLOCK)
						WHERE
							DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
							AND Address = @Address AND @Address IS NOT NULL
							AND Port = @Port AND @Port IS NOT NULL
							AND Generation = @Generation AND @Generation IS NOT NULL
					);

					UPDATE ClusterMembershipVersionTable
					SET
						Timestamp = GETUTCDATE(),
						Version = Version + 1
					WHERE
						DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
						AND Version = @Version AND @Version IS NOT NULL
						AND @@ROWCOUNT > 0;

					SET @ROWCOUNT = @@ROWCOUNT;

					IF @ROWCOUNT = 0
						ROLLBACK TRANSACTION
					ELSE
						COMMIT TRANSACTION
					SELECT @ROWCOUNT;
				');

				INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'UpdateMembershipKey','
					SET XACT_ABORT, NOCOUNT ON;
					BEGIN TRANSACTION;

					UPDATE ClusterMembershipVersionTable
					SET
						Timestamp = GETUTCDATE(),
						Version = Version + 1
					WHERE
						DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
						AND Version = @Version AND @Version IS NOT NULL;

					UPDATE ClusterMembershipTable
					SET
						Status = @Status,
						SuspectTimes = @SuspectTimes,
						IAmAliveTime = @IAmAliveTime
					WHERE
						DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
						AND Address = @Address AND @Address IS NOT NULL
						AND Port = @Port AND @Port IS NOT NULL
						AND Generation = @Generation AND @Generation IS NOT NULL
						AND @@ROWCOUNT > 0;

					SELECT @@ROWCOUNT;
					COMMIT TRANSACTION;
				');

				INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'GatewaysQueryKey','
					SELECT
						Address,
						ProxyPort,
						Generation
					FROM
						ClusterMembershipTable
					WHERE
						DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
						AND Status = @Status AND @Status IS NOT NULL
						AND ProxyPort > 0;
				');

				INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'MembershipReadRowKey','
					SELECT
						v.DeploymentId,
						m.Address,
						m.Port,
						m.Generation,
						m.SiloName,
						m.HostName,
						m.Status,
						m.ProxyPort,
						m.SuspectTimes,
						m.StartTime,
						m.IAmAliveTime,
						v.Version
					FROM
						ClusterMembershipVersionTable v
						-- This ensures the version table will returned even if there is no matching membership row.
						LEFT OUTER JOIN ClusterMembershipTable m ON v.DeploymentId = m.DeploymentId
						AND Address = @Address AND @Address IS NOT NULL
						AND Port = @Port AND @Port IS NOT NULL
						AND Generation = @Generation AND @Generation IS NOT NULL
					WHERE
						v.DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
				');

				INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'MembershipReadAllKey','
					SELECT
						v.DeploymentId,
						m.Address,
						m.Port,
						m.Generation,
						m.SiloName,
						m.HostName,
						m.Status,
						m.ProxyPort,
						m.SuspectTimes,
						m.StartTime,
						m.IAmAliveTime,
						v.Version
					FROM
						ClusterMembershipVersionTable v LEFT OUTER JOIN ClusterMembershipTable m
						ON v.DeploymentId = m.DeploymentId
					WHERE
						v.DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
				');

				INSERT INTO OrleansQuery(QueryKey, QueryText)
				VALUES
				(
					'DeleteMembershipTableEntriesKey','
					DELETE FROM ClusterMembershipTable
					WHERE DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
					DELETE FROM ClusterMembershipVersionTable
					WHERE DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
				');
            ");

			builder.AppendLine(@"
							CREATE TABLE ClusterRemindersTable
							(
								ServiceId NVARCHAR(150) NOT NULL,
								GrainId VARCHAR(150) NOT NULL,
								ReminderName NVARCHAR(150) NOT NULL,
								StartTime DATETIME2(3) NOT NULL,
								Period BIGINT NOT NULL,
								GrainHash INT NOT NULL,
								Version INT NOT NULL,
								CONSTRAINT PK_RemindersTable_ServiceId_GrainId_ReminderName PRIMARY KEY(ServiceId, GrainId, ReminderName)
							);

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'UpsertReminderRowKey','
								DECLARE @Version AS INT = 0;
								SET XACT_ABORT, NOCOUNT ON;
								BEGIN TRANSACTION;
								UPDATE ClusterRemindersTable WITH(UPDLOCK, ROWLOCK, HOLDLOCK)
								SET
									StartTime = @StartTime,
									Period = @Period,
									GrainHash = @GrainHash,
									@Version = Version = Version + 1
								WHERE
									ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									AND GrainId = @GrainId AND @GrainId IS NOT NULL
									AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL;

								INSERT INTO ClusterRemindersTable
								(
									ServiceId,
									GrainId,
									ReminderName,
									StartTime,
									Period,
									GrainHash,
									Version
								)
								SELECT
									@ServiceId,
									@GrainId,
									@ReminderName,
									@StartTime,
									@Period,
									@GrainHash,
									0
								WHERE
									@@ROWCOUNT=0;
								SELECT @Version AS Version;
								COMMIT TRANSACTION;
							');

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'ReadReminderRowsKey','
								SELECT
									GrainId,
									ReminderName,
									StartTime,
									Period,
									Version
								FROM ClusterRemindersTable
								WHERE
									ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									AND GrainId = @GrainId AND @GrainId IS NOT NULL;
							');

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'ReadReminderRowKey','
								SELECT
									GrainId,
									ReminderName,
									StartTime,
									Period,
									Version
								FROM ClusterRemindersTable
								WHERE
									ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									AND GrainId = @GrainId AND @GrainId IS NOT NULL
									AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL;
							');

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'ReadRangeRows1Key','
								SELECT
									GrainId,
									ReminderName,
									StartTime,
									Period,
									Version
								FROM ClusterRemindersTable
								WHERE
									ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									AND GrainHash > @BeginHash AND @BeginHash IS NOT NULL
									AND GrainHash <= @EndHash AND @EndHash IS NOT NULL;
							');

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'ReadRangeRows2Key','
								SELECT
									GrainId,
									ReminderName,
									StartTime,
									Period,
									Version
								FROM ClusterRemindersTable
								WHERE
									ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									AND ((GrainHash > @BeginHash AND @BeginHash IS NOT NULL)
									OR (GrainHash <= @EndHash AND @EndHash IS NOT NULL));
							');

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'DeleteReminderRowKey','
								DELETE FROM ClusterRemindersTable
								WHERE
									ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									AND GrainId = @GrainId AND @GrainId IS NOT NULL
									AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL
									AND Version = @Version AND @Version IS NOT NULL;
								SELECT @@ROWCOUNT;
							');

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'DeleteReminderRowsKey','
								DELETE FROM ClusterRemindersTable
								WHERE
									ServiceId = @ServiceId AND @ServiceId IS NOT NULL;
							');");

            builder.AppendLine(@"
							CREATE TABLE ClusterStorage
							(
								GrainIdHash                INT NOT NULL,
								GrainIdN0                BIGINT NOT NULL,
								GrainIdN1                BIGINT NOT NULL,
								GrainTypeHash            INT NOT NULL,
								GrainTypeString            NVARCHAR(512) NOT NULL,
								GrainIdExtensionString    NVARCHAR(512) NULL,
								ServiceId                NVARCHAR(150) NOT NULL,
								PayloadBinary    VARBINARY(MAX) NULL,
								PayloadXml        XML NULL,
								PayloadJson        NVARCHAR(max) NULL,
								ModifiedOn DATETIME2(3) NOT NULL,
								Version INT NULL
							);
							CREATE NONCLUSTERED INDEX IX_ClusterStorage ON ClusterStorage(GrainIdHash, GrainTypeHash);
							ALTER TABLE ClusterStorage SET(LOCK_ESCALATION = DISABLE);

							IF EXISTS (SELECT 1 FROM sys.dm_db_persisted_sku_features WHERE feature_id = 100)
							BEGIN
								ALTER TABLE ClusterStorage REBUILD PARTITION = ALL WITH(DATA_COMPRESSION = PAGE);
							END

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'WriteToStorageKey',
								'BEGIN TRANSACTION;
								SET XACT_ABORT, NOCOUNT ON;

								DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion;

								IF @GrainStateVersion IS NOT NULL
								BEGIN
									UPDATE ClusterStorage
									SET
										PayloadBinary = @PayloadBinary,
										PayloadJson = @PayloadJson,
										PayloadXml = @PayloadXml,
										ModifiedOn = GETUTCDATE(),
										Version = Version + 1,
										@NewGrainStateVersion = Version + 1,
										@GrainStateVersion = Version + 1
									WHERE
										GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
										AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
										AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
										AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
										AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
										AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
										AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
										AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
										OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));
								END

								IF @GrainStateVersion IS NULL
								BEGIN
									INSERT INTO ClusterStorage
									(
										GrainIdHash,
										GrainIdN0,
										GrainIdN1,
										GrainTypeHash,
										GrainTypeString,
										GrainIdExtensionString,
										ServiceId,
										PayloadBinary,
										PayloadJson,
										PayloadXml,
										ModifiedOn,
										Version
									)
									SELECT
										@GrainIdHash,
										@GrainIdN0,
										@GrainIdN1,
										@GrainTypeHash,
										@GrainTypeString,
										@GrainIdExtensionString,
										@ServiceId,
										@PayloadBinary,
										@PayloadJson,
										@PayloadXml,
										GETUTCDATE(),
										1
									 WHERE NOT EXISTS
									 (
										SELECT 1
										FROM ClusterStorage WITH(XLOCK, ROWLOCK, HOLDLOCK, INDEX(IX_ClusterStorage))
										WHERE
											GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
											AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
											AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
											AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
											AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
											AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
											AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									 ) OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));

									IF @@ROWCOUNT > 0
									BEGIN
										SET @NewGrainStateVersion = 1;
									END
								END

								SELECT @NewGrainStateVersion AS NewGrainStateVersion;
								COMMIT TRANSACTION;'
							);

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'ClearStorageKey',
								'BEGIN TRANSACTION;
								SET XACT_ABORT, NOCOUNT ON;
								DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion;
								UPDATE ClusterStorage
								SET
									PayloadBinary = NULL,
									PayloadJson = NULL,
									PayloadXml = NULL,
									ModifiedOn = GETUTCDATE(),
									Version = Version + 1,
									@NewGrainStateVersion = Version + 1
								WHERE
									GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
									AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
									AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
									AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
									AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
									AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
									AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
									OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));

								SELECT @NewGrainStateVersion;
								COMMIT TRANSACTION;'
							);

							INSERT INTO OrleansQuery(QueryKey, QueryText)
							VALUES
							(
								'ReadFromStorageKey',
								'-- The application code will deserialize the relevant result. Not that the query optimizer
								-- estimates the result of rows based on its knowledge on the index. It does not know there
								-- will be only one row returned. Forcing the optimizer to process the first found row quickly
								-- creates an estimate for a one-row result and makes a difference on multi-million row tables.
								-- Also the optimizer is instructed to always use the same plan via index using the OPTIMIZE
								-- FOR UNKNOWN flags. These hints are only available in SQL Server 2008 and later. They
								-- should guarantee the execution time is robustly basically the same from query-to-query.
								SELECT
									PayloadBinary,
									PayloadXml,
									PayloadJson,
									Version
								FROM
									ClusterStorage
								WHERE
									GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
									AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
									AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
									AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
									AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
									AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
									AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
									OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));'
							);");

            return builder;
        }
    }
}
